const router = require('koa-router')();
const {db, QueryTypes,} = require("../../models/db");
const tool = require("../../tools/tool");


router.post('/contact/bfList', async ctx => {
    let body = ctx.request.body
    let tenantId = ctx.user.tenantId
    let formData = ctx.request.body.formData
    let beginDate = formData.beginDate
    let endDate = formData.endDate
    let salesId = formData.salesId
    let buId = formData.buId
    let excel = body.excel
    let currentPage = ctx.request.body.currentPage?ctx.request.body.currentPage:1
    let pageSize = ctx.request.body.pageSize?ctx.request.body.pageSize:50
    let skip = (currentPage - 1) * pageSize;
    let sql = `select a.id,a.address,a.ctime,a.bfContent ,
       b.name buName,c.name salesName,b.linkMobile ,b.address  buAddress from contact_bf a inner join contact b on a.buId=b.id 
         inner join admin c on a.salesId=c.id where a.tenantId=${tenantId}    `
    if (beginDate) {
        sql = sql + ` and a.ctime >= '${beginDate}' `
    }
    if(endDate)  {
        sql = sql + ` and a.ctime <= '${endDate}' `
    }
    if(buId) {
        sql = sql + ` and a.buId=${buId} `
    }
    if(salesId) {
        sql = sql + ` and a.salesId=${salesId} `
    }
    if(excel) {
        let pageSql = `select * from (${sql}) a order by id desc`

        let list = await db.query(pageSql, {type: QueryTypes.SELECT})

        return tool.doExcelOut(ctx, list)
    }
    else {
        let total = 0
        let totalSql = `select count(*) total from (${sql}) a `
        let totalModel = await db.query(totalSql, {type:QueryTypes.SELECT, plain: true})
        total = totalModel.total
        let pageSql = `select * from (${sql}) a order by id desc 
             offset ${skip} rows fetch next ${pageSize} rows only;`
    
        let list = await db.query(pageSql, {type: QueryTypes.SELECT})
    
        return ctx.suc('', {list,total: total})
    }
    

})
router.post('/contact/bftj', async ctx => {
    let body = ctx.request.body
    let tenantId = ctx.user.tenantId
    let formData = ctx.request.body.formData
    let beginDate = formData.beginDate
    let endDate = formData.endDate
    let salesId = formData.salesId
    let buId = formData.buId
    let currentPage = ctx.request.body.currentPage?ctx.request.body.currentPage:1
    let pageSize = ctx.request.body.pageSize?ctx.request.body.pageSize:50
    let skip = (currentPage - 1) * pageSize;
    let excel = body.excel
    let sql = `select a.id,a.address,a.ctime,a.bfContent ,a.buId,a.salesId,
       b.name buName,c.name salesName,b.linkMobile from contact_bf a inner join contact b on a.buId=b.id 
         inner join admin c on a.salesId=c.id where a.tenantId=${tenantId}    `
    if (beginDate) {
        sql = sql + ` and a.ctime >= '${beginDate}' `
    }
    if(endDate)  {
        sql = sql + ` and a.ctime <= '${endDate}' `
    }
    if(buId) {
        sql = sql + ` and a.buId=${buId} `
    }
    if(salesId) {
        sql = sql + ` and a.salesId=${salesId} `
    }


    if(excel) {
        let pageSql = `select a.salesName,a.salesId,a.buId,a.buName,count(1) times from (${sql}) a
                                                    group by a.salesName,a.salesId,a.buId,a.buName  ;`
        let list = await db.query(pageSql, {type: QueryTypes.SELECT})
        return tool.doExcelOut(ctx, list)
    }
    else {
        let pageSql = `select a.salesName,a.salesId,a.buId,a.buName,count(1) times from (${sql}) a
                                                    group by a.salesName,a.salesId,a.buId,a.buName  ;`

        let list = await db.query(pageSql, {type: QueryTypes.SELECT})
        return ctx.suc('', {list})
    }

})




module.exports = router;